package com.example.application4.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.example.application4.pojo.User;

import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    private DatabaseHelper dbHelper;

    public UserDAO(Context context) {
        dbHelper = new DatabaseHelper(context);
    }

    /**
     * 向数据库中插入用户信息
     * @param username 用户名
     * @param password 密码
     * @param email 邮箱
     * @return 插入成功的行数
     */
    public long insertUser(String username, String password, String email) {
        long result = -1;
        SQLiteDatabase db = null;
        try {
            db = dbHelper.getWritableDatabase(); // 获取可写数据库
            ContentValues values = new ContentValues();
            values.put(DatabaseHelper.COLUMN_USERNAME, username);
            values.put(DatabaseHelper.COLUMN_PASSWORD, password);
            values.put(DatabaseHelper.COLUMN_EMAIL, email);
            result = db.insert(DatabaseHelper.TABLE_USERS, null, values);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (db != null) {
                db.close(); // 关闭数据库连接
            }
        }
        return result;
    }

    /**
     * 验证用户名和密码是否匹配
     * @param username 用户名
     * @param password 密码
     * @return 验证成功返回true，否则返回false
     */
    public boolean validateUser(String username, String password) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        boolean isValid = false;
        try {
            db = dbHelper.getReadableDatabase();
            String selection = DatabaseHelper.COLUMN_USERNAME + " = ? AND " + DatabaseHelper.COLUMN_PASSWORD + " = ?";
            cursor = db.query(DatabaseHelper.TABLE_USERS, new String[]{DatabaseHelper.COLUMN_ID}, selection, new String[]{username, password}, null, null, null);
            isValid = cursor.moveToFirst(); // 检查是否有匹配的记录
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close(); // 关闭游标
            }
            if (db != null) {
                db.close(); // 关闭数据库连接
            }
        }
        return isValid;
    }

    /**
     * 获取所有除当前用户外的用户列表
     * @param currentUsername 当前用户的用户名
     * @return 用户列表
     */
    public List<User> getAllUsersExceptCurrent(String currentUsername) {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = dbHelper.getReadableDatabase();
            String selection = DatabaseHelper.COLUMN_USERNAME + " != ?";
            cursor = db.query(DatabaseHelper.TABLE_USERS, new String[]{DatabaseHelper.COLUMN_ID, DatabaseHelper.COLUMN_USERNAME, DatabaseHelper.COLUMN_EMAIL}, selection, new String[]{currentUsername}, null, null, null); // 查询数据
            while (cursor.moveToNext()) {
                users.add(createUserFromCursor(cursor)); // 将每条记录转换为User对象并添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close(); // 关闭游标
            }
            if (db != null) {
                db.close(); // 关闭数据库连接
            }
        }
        return users;
    }

    /**
     * 获取所有除当前用户外的用户总数
     * @param currentUsername 当前用户的用户名
     * @return 用户总数
     */
    public int getTotalUsersExceptCurrent(String currentUsername) {
        int count = 0;
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = dbHelper.getReadableDatabase(); // 获取可读数据库
            String selection = DatabaseHelper.COLUMN_USERNAME + " != ?";
            cursor = db.query(DatabaseHelper.TABLE_USERS, new String[]{"COUNT(*)"}, selection, new String[]{currentUsername}, null, null, null); // 查询数据
            if (cursor.moveToFirst()) {
                count = cursor.getInt(0); // 获取用户总数
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close(); // 关闭游标
            }
            if (db != null) {
                db.close(); // 关闭数据库连接
            }
        }
        return count;
    }

    /**
     * 获取所有除当前用户外的用户列表，支持分页
     * @param currentUsername 当前用户的用户名
     * @param offset 查询的起始位置
     * @param limit 每页显示的用户数量
     * @return 用户列表
     */
    public List<User> getUsersExceptCurrentWithPagination(String currentUsername, int offset, int limit) {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = dbHelper.getReadableDatabase(); // 获取可读数据库
            String selection = DatabaseHelper.COLUMN_USERNAME + " != ?";
            String orderBy = DatabaseHelper.COLUMN_USERNAME + " ASC";
            cursor = db.query(DatabaseHelper.TABLE_USERS,
                    new String[]{DatabaseHelper.COLUMN_ID, DatabaseHelper.COLUMN_USERNAME, DatabaseHelper.COLUMN_EMAIL},
                    selection,
                    new String[]{currentUsername},
                    null, null, orderBy, offset + "," + limit); // 查询数据
            while (cursor.moveToNext()) {
                users.add(createUserFromCursor(cursor)); // 将每条记录转换为User对象并添加到列表中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close(); // 关闭游标
            }
            if (db != null) {
                db.close(); // 关闭数据库连接
            }
        }
        return users;
    }

    /**
     * 根据用户名查询用户信息
     * @param username 用户名
     * @return 用户信息，如果没有查询到则返回null
     */
    public User getUserByUsername(String username) {
        User user = null;
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = dbHelper.getReadableDatabase(); // 获取可读数据库
            String selection = DatabaseHelper.COLUMN_USERNAME + " = ?";
            cursor = db.query(DatabaseHelper.TABLE_USERS,
                    new String[]{DatabaseHelper.COLUMN_ID, DatabaseHelper.COLUMN_USERNAME, DatabaseHelper.COLUMN_EMAIL},
                    selection, new String[]{username}, null, null, null); // 查询数据
            if (cursor.moveToFirst()) {
                user = createUserFromCursor(cursor); // 将记录转换为User对象
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 记录查询时的错误
        } finally {
            if (cursor != null) {
                cursor.close(); // 关闭游标
            }
            if (db != null) {
                db.close(); // 关闭数据库连接
            }
        }
        return user;
    }

    /**
     * 从Cursor中创建User对象
     */
    private User createUserFromCursor(Cursor cursor) {
        int column_id = cursor.getColumnIndex(DatabaseHelper.COLUMN_ID);
        int column_name = cursor.getColumnIndex(DatabaseHelper.COLUMN_USERNAME);
        int column_email = cursor.getColumnIndex(DatabaseHelper.COLUMN_EMAIL);

        int id = cursor.getInt(column_id);
        String name = cursor.getString(column_name);
        String email = cursor.getString(column_email);
        return new User(id, name, email);
    }
}